--POUR CONTROLE


define erp='erp_mli2.';
define outils='outils_mli2.';

 --CALL uvse_ExistsOrCreate('ART_GENECHAPVRT');

 DELETE FROM se_ART_GENECHAPVRT;
/*
select * from (
SELECT &outils.stragg(source) source,gene_cod, gene_ind, chap_cod, vrt_cod, enreg_id, enreg_s, genechapvrt_rang, genechapvrt_lib, genechapvrt_lib2, genechapvrtoblig_ok, entetevrt_ok, formvrt_ok
FROM (
  SELECT 'uv' source ,g.* FROM se_ART_GENECHAPVRT g
  UNION ALL
  SELECT 'se' source ,g.* FROM &erp.ART_GENECHAPVRT g
  )

GROUP BY gene_cod, gene_ind, chap_cod, vrt_cod, enreg_id, enreg_s, genechapvrt_rang, genechapvrt_lib, genechapvrt_lib2, genechapvrtoblig_ok, entetevrt_ok, formvrt_ok
HAVING Count(1)!=2 ) r
ORDER BY gene_cod,gene_ind,chap_cod, GENECHAPVRT_RANG*/

 INSERT INTO se_ART_GENECHAPVRT
 (SELECT g.gene_cod,
         g.gene_ind,
         g.chap_cod,
         vrt.variantint vrt_cod,
         0 enreg_id,
         'N' enreg_s,
         vrt.RANGMULTI2+1 genechapvrt_rang,
         NULL genechapvrt_lib,
         NULL genechapvrt_lib2,
         decode(tv.typevar,'S','O','C','O','O','N','O') genechapvrtoblig_ok,
         'O' entetevrt_ok,
         decode(tv.typevar,'S','N','C','O','O','N','O') formvrt_ok
    FROM se_art_genechap g, uv_pproduits_variantint vrt,uv_pproduits_typevar tv
    WHERE vrt.ID = g.gene_cod||g.gene_ind
      AND tv.id(+)=vrt.id AND tv.RANGMULTI2(+)=vrt.RANGMULTI2
      --AND g.gene_cod ='TISSU'
      AND EXISTS ( SELECT 1 FROM se_vrt_vrt v WHERE v.vrt_cod=vrt.variantint)
     );

 UPDATE se_ART_GENECHAPVRT tf SET enreg_id=(select enreg_id FROM &erp.ART_GENECHAPVRT f WHERE f.gene_cod=tf.gene_cod AND f.gene_ind=tf.gene_ind AND f.chap_cod=tf.chap_cod AND f.vrt_cod=tf.vrt_cod) ;
 UPDATE se_ART_GENECHAPVRT tf SET enreg_id=&outils.enreg_id.NEXTVAL WHERE enreg_id IS NULL OR enreg_id =0;



 commit;




